package com.modemMgr.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.modemMgr.util.DBAccessor;
import com.modemMgr.vo.Department;

public class DepartmentDAO extends BaseDAO {
	public DepartmentDAO(DBAccessor dbAccessor) {
		super(dbAccessor);
	}

	public List<Department> getDepartmentsByParentId(int parentId) throws Exception
	{
		String sql = "select * from departments where is_valid = 1 and parent_id = ?";
		List<Department> list = dbAccessor.queryList(Department.class, sql, new Object[]{parentId});
		return list;
	}
	
	public Map<Integer, String> getDepartmentsMap() throws Exception
	{
		String sql = "select * from departments where is_valid = 1";
		List<Department> list = dbAccessor.queryList(Department.class, sql);
		Map<Integer, String> map = new HashMap<Integer, String>();
		
		for (Department department : list)
		{
			map.put(department.getDept_id(), department.getDept_name());
		}
		return map;
	}
	
	public Department getParentDepartmentById(int deptId) throws Exception
	{
		String sql = "select a.* from departments a, departments b where a.dept_id = b.parent_id and b.dept_id = ?";
		Department department = dbAccessor.queryObject(Department.class, sql, new Object[]{deptId});
		return department;
	}
	
	public Department getDepartmentById(int deptId, int fieldWorkId) throws Exception
	{
		String sql = "select d.*, (select count(*) from departments a where a.is_valid = 1 and a.parent_id = d.dept_id) + ";
		sql += "(select count(*) from users u, user_role_info r where u.is_valid = 1 and u.dept_id = d.dept_id and u.user_id = r.user_id and r.role_id = ?) ";
		sql += "as child_count from departments d where d.is_valid = 1 and d.dept_id = ?";
		Department department = dbAccessor.queryObject(Department.class, sql, new Object[]{fieldWorkId, deptId});
		return department;
	}
	
	public List<Department> getDepartmentsByParentId(int parentId, int fieldWorkId) throws Exception
	{
		String sql = "select d.*, (select count(*) from departments a where a.is_valid = 1 and a.parent_id = d.dept_id) + ";
		sql += "(select count(*) from users u, user_role_info r where u.is_valid = 1 and u.dept_id = d.dept_id and u.user_id = r.user_id and r.role_id = ?) ";
		sql += "as child_count from departments d where d.is_valid = 1 and d.parent_id = ?";
		List<Department> list = dbAccessor.queryList(Department.class, sql, new Object[]{fieldWorkId, parentId});
		return list;
	}
}
